/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.LocationCostHistory;
import com.ntb.entities.Locations;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author AnhTuan
 */
public class LocationsDAOImpl extends DatabaseConnection implements LocationsDAO {

    public Iterator<Locations> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From Locations");
            while (rs.next()) {
                Locations row = new Locations();
                row.setLocationID(rs.getInt("LocationID"));
                row.setLocationName(rs.getString("LocationName"));
                row.setPresentCost(rs.getInt("PresentCost"));
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Iterator<Locations> getList(String locationName, int page) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageLocations(?, ?, ?)}");
            cst.setString(1, locationName);
            cst.setInt(2, page);
            cst.setInt(3, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Locations row = new Locations();
                row.setLocationID(rs.getInt("LocationID"));
                row.setLocationName(rs.getString("LocationName"));
                row.setPresentCost(rs.getInt("PresentCost"));
                arr.add(row);
            }
            closeConnection();
            if (arr.size() > 0) {
                return arr.iterator();
            }
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<String[]> getCostHistory(int locationID, String year) {
        try {
            List arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_getCostHistory(?, ?)}");
            cst.setInt(1, locationID);
            cst.setString(2, year);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                String[] row = new String[3];
                row[0] = rs.getString("Date");
                row[1] = rs.getString("StandardCost");
                row[2] = rs.getString("AVGCost");
                arr.add(row);
            }
            closeConnection();
            return arr;
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List getCostHistoryYear(int locationID) {
        try {
            List arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_getCostHistoryYear(?)}");
            cst.setInt(1, locationID);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                arr.add(rs.getString("Year"));
            }
            closeConnection();
            return arr;
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int getPageCount(String locationName) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageLocations(?, ?)}");
            cst.setString(1, locationName);
            cst.setInt(2, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Locations getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From Locations Where LocationID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Locations row = new Locations();
            if (rs.next()) {
                row.setLocationID(rs.getInt("LocationID"));
                row.setLocationName(rs.getString("LocationName"));
                row.setPresentCost(rs.getInt("PresentCost"));
                closeConnection();
                return row;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean checkDuplicateName(String LocationName) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From Locations Where LocationName=?");
            pst.setString(1, LocationName);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(Locations object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into Locations(LocationName,PresentCost) Values(?,?)", Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, object.getLocationName());
            pst.setInt(2, object.getPresentCost());

            int result = pst.executeUpdate();

            ResultSet rs = pst.getGeneratedKeys();
            rs.next();

            LocationCostHistoryDAO costDao = new LocationCostHistoryDAOImpl();
            LocationCostHistory cost = new LocationCostHistory();
            cost.setLocationID(rs.getInt(1));
            cost.setStandardCost(object.getPresentCost());
            costDao.add(cost);

            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(Locations object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Locations Set LocationName=?,PresentCost=? Where LocationID=?");
            pst.setString(1, object.getLocationName());
            pst.setInt(2, object.getPresentCost());
            pst.setInt(3, object.getLocationID());

            LocationCostHistoryDAO costDao = new LocationCostHistoryDAOImpl();
            LocationCostHistory cost = costDao.getRow(object.getLocationID());

            boolean flag = false;
            if (cost == null) {
                flag = true;
            } else {
                if (cost.getStandardCost() != object.getPresentCost()) {
                    flag = true;
                }
            }
            if (flag) {
                cost.setLocationID(object.getLocationID());
                cost.setStandardCost(object.getPresentCost());
                costDao.add(cost);
            }
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete Locations Where LocationID=? " +
                    "AND ? NOT IN (SELECT LocationID FROM Lands)");
            pst.setInt(1, id);
            pst.setInt(2, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "Delete Categories Where CategoryID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
